﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBackupRemoveOldBackups]
#-- Purpose:		Deletes backup files that are not necessary to fulfill
#--					the recovery window defined at 
#--					dbo.tblConfiguration.cfg_recover_window_days
#--	Last Update:	04/17/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBackupRemoveOldBackups]
(
	@database_name		sysname
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @physical_device_name nvarchar(4000), @sError nvarchar(4000)
DECLARE @tFiles TABLE (physical_device_name nvarchar(4000))
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spBackupRemoveOldBackups'

--- Build the list of database files NOT needed to meet the Recovery Window Days
INSERT INTO @tFiles
	SELECT		bmf.physical_device_name
	FROM		msdb.dbo.backupset b
	JOIN		msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
	WHERE		b.backup_set_id NOT IN (SELECT backup_set_id FROM dbo.fncGetBackupList_RecoveryWindow(@database_name) t1)
				AND b.database_name = @database_name

--- Delete the unnecessary files
DECLARE crs CURSOR FOR SELECT physical_device_name FROM @tFiles WHERE dbo.FileExists(physical_device_name) = 1
OPEN crs
FETCH NEXT FROM crs INTO @physical_device_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Set the message
	SELECT	@sError = 'Deleting backup file: ' + @physical_device_name
	EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sError

	--- Delete the backup file
	BEGIN TRY
		EXEC	dbo.DeleteFile @filePath = @physical_device_name
	END TRY
	BEGIN CATCH
		SELECT	@sError = 'Failed to delete backup file: ' + @physical_device_name
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sError
	END CATCH
	
	--- Next
	FETCH NEXT FROM crs INTO @physical_device_name
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF